package newsSystem.dao;

import newsSystem.news.NewsInfo;
import newsSystem.news.NewsManager;
import newsSystem.utils.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class NewsInfoJDBC {
//    public static void main(String[] args) {
public static void newsJDBC() {
        //    调用Util里面连接数据库中的类
        Connection connection = null;
        ResultSet resultSet = null;
        Statement statement = null;
        try {
            // 获取数据库连接
            connection = DBUtil.getConnection();

            // 定义 SQL 查询语句
            String sql = "SELECT * FROM newsinfo";

            // 创建 Statement 对象执行查询
            statement = connection.createStatement();

            // 执行查询并获取结果集合
            resultSet = statement.executeQuery(sql);
//         创建一个管理员list列表对象
            List<NewsInfo> NewsList=new ArrayList<>();
            // 处理结果集合
            while (resultSet.next()) {
                // 获取每一行数据的具体字段值
                int newsID = resultSet.getInt("NewsID");
                String newsName = resultSet.getString("NewsName");
                String newsPublish = resultSet.getString("NewsPublish");
                double mPrice = resultSet.getDouble("MPrice");
                String newsContent = resultSet.getString("NewsContent");
                Date newsData = resultSet.getDate("NewsData");
                String newsCNo = resultSet.getString("NewsCNo");
//                String userID = resultSet.getString("UserID");
                byte[] newsImage = resultSet.getBytes("NewsImage");
                String newsDept = resultSet.getString("NewsDept");

                // 打印结果
//                System.out.println("报刊编号: " + NewsID + ", 管理员密码: " + password);
//                System.out.println("NewsID: " + newsID + ", NewsName: " + newsName + ", NewsPublish: " + newsPublish + ", MPrice: " + mPrice + ", NewsContent: " + newsContent + ", NewsData: " + newsData + ", NewsCNo: " + newsCNo + ", UserID: " + userID+",newsImage:"+newsImage);

//            创建管理员对象并且将其添加到集合中去
                NewsInfo NewsInfo=new NewsInfo(newsID,newsName,newsPublish,mPrice,newsContent,newsData,newsCNo,newsImage,newsDept);
//                调用NewsManager类中的添加add方法加入到列表中
                   NewsManager.addNews(NewsInfo);
            }

        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
//            连接结束后释放资源
            DBUtil.closeConnection(connection);
//            释放结果集合
            DBUtil.closeResultSet(resultSet);
//            释放Statement 对象
            DBUtil.closePreparedStatement(statement);
        }
    }
//添加报刊信息操作
    public static void saveNewsToDatabase(NewsInfo news) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            // 获取数据库连接
            connection = DBUtil.getConnection();

            // 定义 SQL 插入语句
            String sql = "INSERT INTO newsinfo (NewsID, NewsName, NewsPublish, MPrice, NewsContent, NewsData, NewsCNo, NewsImage, NewsDept) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";

            // 使用 PreparedStatement 预编译 SQL 语句
            preparedStatement = connection.prepareStatement(sql);

            // 设置参数值
            preparedStatement.setInt(1, news.getNewsID());
            preparedStatement.setString(2, news.getNewsName());
            preparedStatement.setString(3, news.getNewsPublish());
            preparedStatement.setDouble(4, news.getMPrice());
            preparedStatement.setString(5, news.getNewsContent());
            preparedStatement.setDate(6, new java.sql.Date(news.getNewsData().getTime()));
            preparedStatement.setString(7, news.getNewsCNo());
            preparedStatement.setBytes(8, news.getNewsImage());
            preparedStatement.setString(9, news.getNewsDept());

            // 执行插入操作
            preparedStatement.executeUpdate();
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            // 释放资源
            DBUtil.closeConnection(connection);
            DBUtil.closePreparedStatement(preparedStatement);
        }
    }

//    删除报刊信息操作
    public static void newsDeleteJDBC(int newsIDToDelete) {
        Connection connection = null;
        ResultSet resultSet = null;
        Statement statement = null;
        try {
            // 获取数据库连接
            connection = DBUtil.getConnection();

            // 定义 SQL 查询语句
            String sql = "SELECT * FROM newsinfo";

            // 创建 Statement 对象执行查询
            statement = connection.createStatement();

            // 执行查询并获取结果集合
            resultSet = statement.executeQuery(sql);

            // 处理结果集合
            while (resultSet.next()) {
                // 获取每一行数据的具体字段值
                int newsID = resultSet.getInt("NewsID");

                // 判断当前新闻对象的 NewsID 是否与要删除的 NewsID 匹配
                if (newsID == newsIDToDelete) {
                    // 如果匹配，则执行删除操作
                    deleteNewsByID(newsID);
                }
            }

        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            // 释放资源
            DBUtil.closeConnection(connection);
            DBUtil.closeResultSet(resultSet);
            DBUtil.closePreparedStatement(statement);
        }
    }

    // 删除指定报刊编号的新闻信息
    public static void deleteNewsByID(int newsID) throws SQLException {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            // 获取数据库连接
            connection = DBUtil.getConnection();

            // 构造删除语句
            String deleteSQL = "DELETE FROM newsinfo WHERE NewsID = ?";
            preparedStatement = connection.prepareStatement(deleteSQL);
            preparedStatement.setInt(1, newsID);

            // 执行删除操作
            int rowsAffected = preparedStatement.executeUpdate();
            if (rowsAffected > 0) {
                System.out.println("报刊信息删除成功");
            } else {
                System.out.println("未找到匹配的报刊信息");
            }
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } finally {
            // 关闭资源
            DBUtil.closeConnection(connection);
            DBUtil.closePreparedStatement(preparedStatement);
        }
    }

//修改报刊信息
public static void updateNewsInDatabase(NewsInfo news) {
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    try {
        // 获取数据库连接
        connection = DBUtil.getConnection();

        // 定义 SQL 更新语句
        String sql = "UPDATE newsinfo SET NewsName=?, NewsPublish=?, MPrice=?, NewsContent=?, NewsData=?, NewsCNo=?, NewsImage=?, NewsDept=? WHERE NewsID=?";

        // 使用 PreparedStatement 预编译 SQL 语句
        preparedStatement = connection.prepareStatement(sql);

        // 设置参数值
        preparedStatement.setString(1, news.getNewsName());
        preparedStatement.setString(2, news.getNewsPublish());
        preparedStatement.setDouble(3, news.getMPrice());
        preparedStatement.setString(4, news.getNewsContent());
        preparedStatement.setDate(5, new java.sql.Date(news.getNewsData().getTime()));
        preparedStatement.setString(6, news.getNewsCNo());
        preparedStatement.setBytes(7, news.getNewsImage());
        preparedStatement.setString(8, news.getNewsDept());
        preparedStatement.setInt(9, news.getNewsID());

        // 执行更新操作
        preparedStatement.executeUpdate();
    } catch (ClassNotFoundException | SQLException e) {
        e.printStackTrace();
    } finally {
        // 释放资源
        DBUtil.closeConnection(connection);
        DBUtil.closePreparedStatement(preparedStatement);
    }
}



}